﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

namespace MaLu.Data
{
    public sealed class SqlHelper : BaseEntityHelper, IDbHelper {
        private static SqlHelper instance = null;

        public static SqlHelper Instance {
            get {
                if (instance == null) {
                    instance = new SqlHelper();
                }
                return instance;
            }
        }

        #region 数据库操作



        public int ExecuteNonQuery(IDbProvider provider, string sqlString) {
            return ExecuteNonQuery(provider, sqlString, null);
        }

        public int ExecuteNonQuery(IDbProvider provider, string sqlString, params IDataParameter[] sqlParas) {
            provider.Command.CommandText = sqlString;
            provider.Command.CommandType = CommandType.Text;
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(sqlParas);
            provider.AddQuery();
            return provider.Command.ExecuteNonQuery();
        }

        public object ExecuteScalar(IDbProvider provider, string sqlString) {
            return ExecuteScalar(provider, sqlString, null);
        }

        public object ExecuteScalar(IDbProvider provider, string sqlString, params IDataParameter[] sqlParas) {
            provider.Command.CommandText = sqlString;
            provider.Command.CommandType = CommandType.Text;
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(sqlParas);
            provider.AddQuery();
            return provider.Command.ExecuteScalar();
        }

        public IDataReader ExecuteDataReader(IDbProvider provider, string sqlString) {
            return ExecuteDataReader(provider, sqlString, null);
        }

        public IDataReader ExecuteDataReader(IDbProvider provider, string sqlString, params IDataParameter[] sqlParas) {
            provider.Command.CommandText = sqlString;
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(sqlParas);
            provider.AddQuery();
            return provider.Command.ExecuteReader();
        }

        public DataTable ExecuteDataTable(IDbProvider provider, string sqlString) {
            return ExecuteDataTable(provider, sqlString, null);
        }

        public DataTable ExecuteDataTable(IDbProvider provider, string sqlString, params IDataParameter[] sqlParas) {
            provider.Command.CommandText = sqlString;
            provider.Command.CommandType = CommandType.Text;
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(sqlParas);
            DataSet ds = new DataSet();
            provider.DataAdapter.Fill(ds);
            provider.AddQuery();
            return ds.Tables[0];
        }

        public DataSet ExecuteProcedure(IDbProvider provider, string ProcedureName, params IDataParameter[] sqlParas)
        {
            provider.Command.CommandText = ProcedureName;
            provider.Command.CommandType = CommandType.StoredProcedure;
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(sqlParas);
            DataSet ds = new DataSet();
            provider.DataAdapter.Fill(ds);
            provider.AddQuery();
            return ds;

        }
        #endregion

        #region 创建DataParameter

        public IDataParameter CreateParameter(string name) {
            return CreateParameter(name, null);
        }

        public IDataParameter CreateParameter(string name, object value) {
            SqlParameter p = new SqlParameter(name, value);
            return p;
        }

        public IDataParameter CreateParameter(string name, DataType type) {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            return p;
        }

        public IDataParameter CreateParameter(string name, DataType type, int size) {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            if (size > 0)
                p.Size = size;
            return p;
        }

        public IDataParameter CreateParameter(string name, DataType type, int size, object value) {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            if (size > 0)
                p.Size = size;
            p.Value = value;
            return p;
        }

        public IDataParameter CreateParameter(string name, DataType type,ParameterDirection dir)
        {
            SqlParameter p = new SqlParameter(name, ConvertType(type));                
            p.Direction = dir;
            return p;
        }

        public IDataParameter CreateParameter(string name, DataType type, object value) {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            p.Value = value;
            return p;
        }

        #endregion

        #region EntityHelper

        public string CreateInsertSql(IEntity entity, out IDataParameter[] paras)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            ColumnAttribute[] cas = GetColumnInfo(entity);
            TableAttribute ta = GetTableInfo(entity);
            List<IDataParameter> paraList = new List<IDataParameter>();
            sb.AppendFormat("insert into [{0}](", ta.Name);
            sb2.Append(" values(");
            foreach (ColumnAttribute ca in cas)
            {
                if (!ca.AutoIncrement)
                {
                    sb.AppendFormat("[{0}]", ca.Name);
                    sb.Append(",");
                    sb2.Append("@" + ca.Name + ",");
                    paraList.Add(CreateParameter("@" + ca.Name, ca.Type, ca.Length, GetPropertyValue(entity, ca.Name)));
                }
            }
            sb.Remove(sb.Length - 1, 1);
            sb2.Remove(sb2.Length - 1, 1);
            sb.Append(")");
            sb2.Append(")");
            paras = paraList.ToArray();
            return sb.ToString() + sb2.ToString();
        }

        public string CreateUpdateSql(IEntity entity, out IDataParameter[] paras)
        {
            StringBuilder sb = new StringBuilder();
            string whereString = string.Empty;
            ColumnAttribute[] cas = GetColumnInfo(entity);
            TableAttribute ta = GetTableInfo(entity);
            List<IDataParameter> paraList = new List<IDataParameter>();
            sb.AppendFormat("update [{0}] set ", ta.Name);
            foreach (ColumnAttribute ca in cas)
            {
                if (ca.PrimaryKey)
                {
                    whereString = string.Format(" where [{0}]=@{0}", ca.Name);
                }
                else
                {
                    sb.AppendFormat("[{0}]=@{0}, ", ca.Name);
                }
                paraList.Add(CreateParameter("@" + ca.Name, ca.Type, ca.Length, GetPropertyValue(entity, ca.Name)));
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append(whereString);
            paras = paraList.ToArray();
            return sb.ToString();
        }

        public string CreateSelectParameters(IEntity entity, string tableName)
        {
            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(tableName))
                tableName += ".";
            else
                tableName = string.Empty;
            ColumnAttribute[] cs = GetColumnInfo(entity);
            LinkTableAttribute[] lt = GetLinkTable(entity);
            LinkTablesAttribute[] lts = GetLinkTables(entity);
            foreach (ColumnAttribute item in cs)
            {
                sb.AppendFormat("{0}.{1} as {2},", tableName, item.Name, item.Name);
            }
            foreach (LinkTableAttribute item in lt)
            {
                sb.AppendFormat("{0}.{1} as {2}_{3},", item.Name);
            }
            return string.Empty;
        }
        public EType ConvertToEntity<EType>(DataRow dr) where EType : IEntity, new()
        {
            EType entity = default(EType);

            entity = EntityFactory.CreateInstance<EType>();
            foreach (var pi in GetInfo(entity.GetType()).Properties)
            {
                if (pi.Property.GetCustomAttributes(typeof(ColumnAttribute), false).Length > 0)
                {
                    pi.Set(entity, ConvertValue(pi.Property.PropertyType, dr[pi.Property.Name]));

                }
            }

            return entity;
        }

        public EType ConvertToEntity<EType>(IDataReader reader) where EType : IEntity, new()
        {
            return ConvertToEntity<EType>(reader, string.Empty);
        }

        public IList<EType> ConvertToList<EType>(IDataReader reader) where EType : IEntity, new()
        {
            return ConvertToList<EType>(reader, string.Empty);
        }

        public EType ConvertToEntity<EType>(IDataReader reader, string prefix) where EType : IEntity, new()
        {
            EType entity = default(EType);
            if (reader.Read())
            {
                entity = EntityFactory.CreateInstance<EType>();
                foreach (var pi in GetInfo(entity.GetType()).Properties)
                {
                    if (pi.Property.GetCustomAttributes(typeof(ColumnAttribute), false).Length > 0)
                        pi.Set(entity, ConvertValue(pi.Property.PropertyType, reader[prefix + pi.Property.Name]));
                }
            }
            return entity;
        }

        public IList<EType> ConvertToList<EType>(IDataReader reader, string prefix) where EType : IEntity, new()
        {
            IList<EType> entityList = new List<EType>();
            while (reader.Read())
            {
                EType entity = EntityFactory.CreateInstance<EType>();
                foreach (var pi in GetInfo(typeof(EType)).Properties)
                {
                    if (pi.Property.GetCustomAttributes(typeof(ColumnAttribute), false).Length > 0)
                        pi.Set(entity, ConvertValue(pi.Property.PropertyType, reader[prefix + pi.Property.Name]));
                }
                entityList.Add(entity);
            }
            return entityList;
        }

        public EType ConvertSomeToEntity<EType>(IDataReader reader) where EType : IEntity, new()
        {
            return ConvertSomeToEntity<EType>(reader, string.Empty);
        }

        public IList<EType> ConvertSomeToList<EType>(IDataReader reader) where EType : IEntity, new()
        {
            return ConvertSomeToList<EType>(reader, string.Empty);
        }

        public EType ConvertSomeToEntity<EType>(IDataReader reader, string prefix) where EType : IEntity, new()
        {
            EType entity = default(EType);
            if (reader.Read())
            {
                entity = EntityFactory.CreateInstance<EType>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    string name = reader.GetName(i);
                    var array = GetInfo(entity.GetType()).Properties;
                    foreach (var pi in array)
                    {
                        if (prefix + pi.Property.Name == name)
                        {
                            pi.Set(entity, ConvertValue(pi.Property.PropertyType, reader[i]));
                            break;
                        }
                    }
                }
            }
            return entity;
        }

        public IList<EType> ConvertSomeToList<EType>(IDataReader reader, string prefix) where EType : IEntity, new()
        {
            IList<EType> entityList = new List<EType>();
            while (reader.Read())
            {
                EType entity = EntityFactory.CreateInstance<EType>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    string name = reader.GetName(i);
                    var array = GetInfo(entity.GetType()).Properties;
                    foreach (var pi in array)
                    {
                        if (prefix + pi.Property.Name == name)
                        {
                            pi.Set(entity, ConvertValue(pi.Property.PropertyType, reader[i]));
                            break;
                        }
                    }
                }
                entityList.Add(entity);
            }
            return entityList;
        }

        #endregion

        #region 反射属性部分

        public object GetPropertyValue(IEntity entity, string name)
        {
            PropertyInfo pi = entity.GetType().GetProperty(name);
            object result = null;
            if (pi != null)
            {
                result = pi.GetValue(entity, null);
            }
            return result;
        }

        public VType GetPropertyValue<VType>(IEntity entity, string name)
        {
            object result = GetPropertyValue(entity, name);
            if (result == null)
                return default(VType);
            else
                return (VType)GetPropertyValue(entity, name);
        }

        public void SetPropertyValue(IEntity entity, string name, object value)
        {
            PropertyInfo pi = entity.GetType().GetProperty(name);
            if (pi != null)
            {
                pi.SetValue(entity, value, null);
            }
        }

        #endregion

        #region 其他私有方法

        private SqlDbType ConvertType(DataType type) {
            SqlDbType sqlType = SqlDbType.BigInt;
            switch (type) {
                case DataType.Binary:
                    sqlType = SqlDbType.Binary;
                    break;
                case DataType.Bit:
                    sqlType = SqlDbType.Bit;
                    break;
                case DataType.Char:
                    sqlType = SqlDbType.Char;
                    break;
                case DataType.Date:
                    sqlType = SqlDbType.Date;
                    break;
                case DataType.DateTime:
                    sqlType = SqlDbType.DateTime;
                    break;
                case DataType.Decimal:
                    sqlType = SqlDbType.Decimal;
                    break;
                case DataType.Double:
                    sqlType = SqlDbType.Float;
                    break;
                case DataType.Float:
                    sqlType = SqlDbType.Float;
                    break;
                case DataType.GUID:
                    sqlType = SqlDbType.UniqueIdentifier;
                    break;
                case DataType.Image:
                    sqlType = SqlDbType.Image;
                    break;
                case DataType.Int16:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.Int32:
                    sqlType = SqlDbType.Int;
                    break;
                case DataType.Int4:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.Int64:
                    sqlType = SqlDbType.BigInt;
                    break;
                case DataType.Int8:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.Text:
                    sqlType = SqlDbType.NText;
                    break;
                case DataType.UnsignedInt16:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.UnsignedInt32:
                    sqlType = SqlDbType.Int;
                    break;
                case DataType.UnsignedInt4:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.UnsignedInt8:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.VarChar:
                    sqlType = SqlDbType.NVarChar;
                    break;
            }

            return sqlType;
        }

        private object ConvertValue(Type type, object value) {
            if (value == DBNull.Value)
                return null;
            return Convert.ChangeType(value, type);
        }

        public VType ConvertValue<VType>(Type type, object value) {
            object result = ConvertValue(type, value);
            if (result == null)
                return default(VType);
            else
                return (VType)result;
        }

        #endregion
    }
}
